1. Field of the Invention
The present invention relates to a system and method for managing database systems.
2. Description of Related Art
As database management systems (DBMS) continue to increase in function and expand into new application areas, the diversity of database workloads is increasing as well. In addition to the classic relational DBMS workload consisting of short transactions running concurrently with long decision support queries, workloads comprising of an even wider range of system demands are emerging. New complex data types, such as image files, audio files, video files and other large objects, and new active data warehouse requirements, such as capacity on demand, data replication, fault-tolerance, dual active query processing, recursion, user defined types (UDFs), external UDFs, and so on, result in widely varying memory, processor, disk and network demands on database systems.
In general, a DBMS has a number of operational characteristics. These include physical statistics, such as CPU usage, query response times and performance statistics. In some DBMS, the operational characteristics include rule sets under which the database operates, relating to the likes of resource consumption and request prioritization. Varying these rule sets often has an effect on other physical characteristics, for example altering performance statistics. Ideally, a DBMS should be able to accept performance goals for a workload and dynamically adjust its own performance based on whether or not these goals are being met.
Closed-loop system management (CLSM) is a technology directed towards this ideal. Under some known CLSM-type systems, incoming queries are split into workload groups, each workload group having respective service level goals (SLGs). The DBMS is responsive to whether or not these goals are met for selectively switching between predetermined rule sets or adjusting performance controls.
It is also known to operate multi-system environments, wherein a plurality of databases, database systems, or DBMS operate in parallel. For example, DBMS that use a Massively Parallel Processing (MPP) architecture across multiple systems or a Symmetric Multiprocessing (SMP) architecture. In particular, it is known to operate a “dual-active” system wherein a plurality of databases operate in parallel and intercommunicate. It will be appreciated that managing complex workloads and performance goals performance objectives across the board in a multi-system environment is difficult.
Moreover, the disk drive storage technology used by database systems has been delivering rapid capacity growth and slow random performance improvement, creating a divergence that presents performance challenges for the database. The number of accesses a disk drive can perform is a function of seek time, rotation rate, and transfer rate, and transfer size. A disk drive can do a certain amount of small I/Os or a smaller number of large I/Os generating more throughput, or some combination in between.
User workloads are generally a mix of work requiring both small and large I/Os. However, these workloads may need access to the same data, so a tradeoff is required on setting the block size of the database object. One potential solution is to self-manage different block sizes for each database object. However, this is wasteful of expensive human resources, is not continuous, and is prone to mistakes or incomplete tuning that reduce efficiency.
Consequently, there is a need in the art for additional management techniques for use in database systems. Specifically, there is a need for systems that can automate the tuning of physical and logical database structures. The present invention satisfies this need.